1. Technical Field
Present invention embodiments relate to database query optimization, and more specifically, to optimizing the database access plan adaptively at query execution.
2. Discussion of the Related Art
One of the most significant challenges in query optimization is accuracy in selectivity (filter factor) estimation for simple and compound predicates. For simple predicates with literal values, solutions exist regarding statistics collection of frequency statistics and/or histogram (quantiles) to improve selectivity estimation. However, statistics collection and identification of the need for statistics can be cumbersome.
The majority of online transaction processing (OLTP) applications in a relational database management system use host variables or parameter markers to allow re-use of the same query statement. This means that at access path selection time, the actual literals are not yet known, and thus frequency or histogram statistics become less effective.
One current technique to address this issue is by re-optimization of the access path once the literals are known. This adds overhead of requiring a new re-optimization of the statement at each execution, and this overhead results in reduced adoption of this feature for customers with online transactional workloads.
Other techniques include parametric plans, where multiple plans are stored and switched between each stored plan at execution time based upon the literals used. The downfall of this approach is the need to either predict which combinations of filtering may occur or to track and store a minimum number of the most common plans at execution time. This technique still requires re-optimization when a candidate plan is not already stored, and also introduces challenges to determine the ranges of selectivity when each plan is optimal. Complexity of such a solution increases as the number of predicates increase.
Random sampling of literals has also been explored, whereby either a single weighted. average access path is produced or one access path per literal set. However, this option targets repeating queries and therefore does not address cases where filtering changes dramatically with each query statement execution. Also, this technique involves a learning period so that the representative sample can be captured.
Accurately estimating actual execution time filtering in a practical and efficient manner for high volume transaction workloads is an ongoing research and development concern. Despite significant research in this field, an efficient solution for mission critical applications remains elusive.